package com.xebest.util;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;


public class ExcelWriter {

    /**
     * 实例工作簿对象
     */
    private HSSFWorkbook workbook = null;

    /**
     * 定义表格
     */
    private HSSFSheet sheet;

    /**
     * 工作簿对象
     *
     * @param sheetName
     */
    public ExcelWriter(String sheetName) {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
    }

    public ExcelWriter(String sheetName, String tempUrl)
            throws FileNotFoundException, IOException {

        workbook = new HSSFWorkbook(new FileInputStream(tempUrl));
        sheet = workbook.getSheetAt(0);
    }

    /**
     * 创建单元对象
     *
     * @param rowIndex 行的
     * @param colIndex 单元格行
     * @param value    设置
     * @param style    样式
     * @param width    宽度
     * @return 返回单元格对象
     */
    public HSSFCell createCell(Integer rowIndex, Integer colIndex,
                               String value, HSSFCellStyle style, Integer width) {

        HSSFRow row = sheet.createRow(rowIndex.shortValue());
        HSSFCell cell = row.createCell(colIndex);

        cell.setCellValue(new HSSFRichTextString(value));
        cell.setCellStyle(style);
        sheet.setColumnWidth(colIndex, width.shortValue());
        return cell;
    }

    public HSSFCell createCell(Integer rowIndex, Integer colIndex, String value) {

        HSSFRow row = sheet.createRow(rowIndex.shortValue());
        HSSFCell cell = row.createCell(colIndex);

        cell.setCellValue(new HSSFRichTextString(value));

        return cell;
    }

    /**
     * 创建样式
     *
     * @return 返回样式
     */
    public HSSFCellStyle createDefaultStyle(HSSFFont font, Short alignment) {

        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(alignment);
        return style;
    }

    /**
     * 获得工作簿
     *
     * @return 工作簿对象
     */
    public HSSFWorkbook getWorkbook() {
        return workbook;
    }

    /**
     * 导出到Excel
     *
     * @param list   业务数据
     * @param column 列名
     * @author LBB
     */
    public static void dealExcel(HttpServletResponse response, List<?> list, String[] column, String fileName) throws Exception {
        ExcelWriter writer = new ExcelWriter("sheet");
        HSSFWorkbook workbook = writer.getWorkbook();
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFFont.SS_NONE);

        HSSFFont fontHead = workbook.createFont();

        fontHead.setColor(HSSFFont.SS_NONE);
        fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        HSSFCellStyle style = writer.createDefaultStyle(font,
                HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle styleColor = workbook.createCellStyle();

        styleColor.setFillForegroundColor(HSSFColor.LIME.index);
        styleColor.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        styleColor.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont fontNo = workbook.createFont();
        fontNo.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        styleColor.setFont(fontNo);

        HSSFCellStyle styleNote = workbook.createCellStyle();
        HSSFFont fontNote = workbook.createFont();
        styleNote.setFont(fontNote);

		/*HSSFCellStyle styleHead = writer.createDefaultStyle(fontHead,
				HSSFCellStyle.ALIGN_CENTER);*/

        HSSFSheet sheet = workbook.getSheetAt(0);
        //sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 9));
        //sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 9));
        // sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));


        // 设置字体大小
        HSSFFont celltbNameFont = workbook.createFont();
        celltbNameFont.setFontHeightInPoints((short) 15);
        celltbNameFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置顶部样式
        HSSFCellStyle styleCell = workbook.createCellStyle();
        styleCell.setFont(celltbNameFont);
        styleCell.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //HSSFRow rows = sheet.createRow((short) 0);// 创建一行
        //rows.setHeight((short) 600);

        //HSSFCell cells = rows.createCell(0);
        styleCell.setWrapText(true);
        //	cells.setCellValue(new HSSFRichTextString(title));//设置标题
        //	cells.setCellStyle(styleCell);

        //sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 9));
        //sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
        //HSSFRow row = sheet.createRow((short) 1);// 创建一行
        //  row.setHeight((short)500);


        //HSSFCell cell = row.createCell(0);

        // 新增的四句话，设置CELL格式为文本格式 2012-04-20
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        cellStyle2.setDataFormat(format.getFormat("@"));
        //cell.setCellStyle(cellStyle2);

        styleNote.setWrapText(true);

        //cell.setCellValue(new HSSFRichTextString(describtion));//标题描述
        //cell.setCellStyle(styleNote);
        //sheet.setColumnWidth(0, (short) (256 * 27));

        HSSFRow row2 = sheet.createRow(0);
        row2.setHeight((short) 440);
        for (int i = 0; i < column.length; i++) {
            HSSFCell cell2 = row2.createCell(i);
            cell2.setCellValue(new HSSFRichTextString(column[i]));
            cell2.setCellStyle(styleColor);
            sheet.setColumnWidth(i, (int) (256 * 27));
        }

        //写入业务数据
        for (int i = 0; i < list.size(); i++) {
            HSSFRow rowBusiness = sheet.createRow(i + 1);
            String[] businessRow = (String[]) list.get(i);
            for (int j = 0; j < businessRow.length; j++) {
                HSSFCell cellBusiness = rowBusiness.createCell(j);
                cellBusiness.setCellValue(new HSSFRichTextString(businessRow[j]));
                cellBusiness.setCellStyle(style);
                sheet.setColumnWidth(j, (int) (256 * 27));
                //writer.createCell(i+3, j, businessRow[j], style, (int)(256 * 27));//将list中的数据存入excel
            }
        }

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        response.setHeader("Cache-Control", "must-revalidate,post-check=0,pre-check=0");
        response.setHeader("Pragma", "public");
        response.setDateHeader("Expires", 0);
        OutputStream os = response.getOutputStream();
        workbook.write(os);//写入数据流
        os.flush();
        os.close();

    }
}
